package com.mzj.saas.mservice.lock.Repository;

import com.mzj.saas.mservice.lock.entity.SmartLockAuth;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.PagingAndSortingRepository;
import org.springframework.transaction.annotation.Transactional;

import java.util.List;

public interface SmartLockAuthRepository extends PagingAndSortingRepository<SmartLockAuth, Long>, JpaSpecificationExecutor<SmartLockAuth> {
    List<SmartLockAuth> findByLockId(Long lockId);

    List<SmartLockAuth> findByHouseId(String houseId);
    List<SmartLockAuth> findByHouseIdAndBindType(String houseId, Integer bindType);
    SmartLockAuth findByHouseIdAndAuthorUserId(String houseId, String userId);

    SmartLockAuth findSmartLockAuthById(Long id);

    @Query("SELECT COUNT(a) FROM SmartLockAuth a WHERE a.lockId = ?1 and a.bindType = 0 AND (a.pwdStatus = 2 OR a.fingerprintStatus = 2 OR a.cardStatus = 2 OR a.cpuStatus = 2 or a.bleStatus = 2 or a.faceStatus = 2)")
    int findSmartLockAuthByLockId(Long lockId);

    @Query("SELECT COUNT(a) FROM SmartLockAuth a WHERE a.lockId = ?1 and a.bindType = 1 AND (a.pwdStatus = 2 OR a.fingerprintStatus = 2 OR a.cardStatus = 2 OR a.cpuStatus = 2 or a.bleStatus = 2 or a.faceStatus = 2)")
    int findPersonSmartLockAuthByLockId(Long lockId);

    List<SmartLockAuth> findByLockIdAndBindType(Long lockId, Integer bindType);

    @Query("SELECT a FROM SmartLockAuth a WHERE a.lockId = ?1 and a.bindType = ?2 AND (a.pwdStatus in (1,2) OR a.fingerprintStatus in (1,2) OR a.cardStatus in (1,2) OR a.cpuStatus in (1,2) or a.bleStatus in (1,2) or a.faceStatus in (1,2))")
    List<SmartLockAuth> findByLockIdAndBindTypeAndStatus(Long lockId, Integer bindType);

    int deleteByLockId(Long lockId);

    List<SmartLockAuth> findByAuthorUserId(String authorUserId);

    SmartLockAuth findByHouseIdAndMobile(String houseId, String mobile);

    @Query("SELECT a FROM SmartLockAuth a WHERE a.authorUserId = ?1 ")
    List<SmartLockAuth> findByAuthorUserIdList(String authorUserId);


    SmartLockAuth findByMatchUserId(String matchUserId);

    List<SmartLockAuth> findByMobile(String mobile);

    @Query(value = "SELECT * FROM saas_smart_lock_auth WHERE  mobile = ?1", nativeQuery = true)
    List<SmartLockAuth> findByMobileList(String mobile);

    List<SmartLockAuth> findByLockIdIn(List<Long> lockIds);

    @Query("SELECT a FROM SmartLockAuth a WHERE a.houseId = ?1 AND a.matchUserId=?2")
    List<SmartLockAuth> finHouseIdAndMatchUserId(String houseId, String matchUserId);

    List<SmartLockAuth> findByIdIn(List<Long> lockIds);

    @Modifying
    @Query(value = "UPDATE saas_smart_lock_auth SET match_user_id = ?2 WHERE lock_id = ?1 and bind_type = 0", nativeQuery = true)
    int updateMatchUserId(Long lockId, String matchUserId);

    List<SmartLockAuth> findByHouseIdAndBindTypeAndMobile(String houseId, Integer bindType, String mobile);

    @Query(value = "SELECT * FROM saas_smart_lock_auth WHERE (author_user_id = ?1 OR mobile = ?2) AND bind_type = 0", nativeQuery = true)
    List<SmartLockAuth> findOrgLockAuth(String authorUserId, String mobile);

    SmartLockAuth findByCommunityUserId(Long communityUserId);

    @Modifying
    @Query(value = "DELETE FROM saas_smart_lock_auth WHERE lock_id = ?1 AND bind_type = 1", nativeQuery = true)
    void deletePersonAuth(Long lockId);

    List<SmartLockAuth> findBySubHouseId(String houseId);

    @Query(value ="select mac from saas_smart_lock s WHERE id in(select lock_id from saas_smart_lock_auth WHERE id = ?1)",nativeQuery = true)
    String BluetoothKey(Long id);

    @Query(value ="select mac from saas_smart_lock s WHERE id in(select lock_id from saas_smart_lock_auth WHERE id = ?1)",nativeQuery = true)
    String mobile(Long id);

    @Query(value ="select * from saas_smart_lock_auth where house_id=?1)",nativeQuery = true)
    SmartLockAuth house(String id);

    @Query(value ="select * from saas_smart_lock_auth WHERE identification=1?)",nativeQuery = true)
    SmartLockAuth getIdentification(String id);

    @Transactional
    @Modifying
    @Query(value = "UPDATE saas_smart_lock_auth set identification=?1 where id=?2", nativeQuery = true)
    int setIdentification(String uuid, Long id);

    SmartLockAuth findByAuthorUserIdAndLockId(String userId, Long lockId);

    SmartLockAuth findByHouseIdAndCommunityUserId(String houseId, Long id);


    List<SmartLockAuth> findByCommunityUserIdIn(List<Long> communityHouseUserIds);
}
